DIY Exercise 12-1: Read and write files using the File, FTP, and Database connectors

Time estimate: 3 hours

Objectives

In this exercise, you connect Mule applications and other systems together using various connectors. You will:

·       Insert files into a database using the Database connector.

·       Write files using the FTP connector.

·       Write files using the File connector.

Scenario

You are assigned to a project involving the company's banking branch, Mule Bank. The bank has partnered with several retail partners to distribute gift cards that are funded by the bank but handled and processed by Visa. The bank has decided to use tightly coupled solutions for their partners who are not yet supporting REST or SOAP.

 

Gift card numbers are generated from your credit card processing partner Visa and are then uploaded in bulk as CSV files to the Mule bank's middleware server. Each partner's new Visa gift card numbers need to be stored in a separate CSV file for that partner. Your task is to develop a network of Mule applications that will retrieve these Visa gift card CSV files and then route and process them to the correct partner's receiving servers.

 

Each partner will use a different technology to receive their lists of Visa gift cards:

·       The Food-N-Savings retail partner has set up a database table named Giftcards to receive gift cards. In order to successfully insert each row in the CSV file into the database, you will need to property map each Visa gift card row in the CSV file to the correct database columns.

·       The Meals-N-Go retail partner has set up an FTP server in their DMZ (demilitarized zone) to receive gift cards. They have an existing processing system that expects gift cards in a specific CSV format that is different from the Visa gift card CSV format, so you will have to map from the Visa gift card CSV file format to the Meals-N-Go CSV format.

·       The Online Retail Plus partner is a little more modern. They already have an Enterprise Service Bus implementation with a JMS messaging bus. They also already have a JMS message format set up to receive and process gift cards, so you will have to map from the Visa gift card CSV format to the JMS message format.

Note: For ease of development and testing, you will first create all the flows for all Mule applications in one Mule application. This helps you rapidly create and test the Process API logic and data mappings. In the next exercise, you will refactor these flows into separate Mule applications to be distributed to different partners, which can communicate with each other using an Enterprise messaging service like JMS.

Import the starting project

Import /files/module12/connectors-mod12-various-systems-starter.jar in the MUFundamentals4.x DIY Files zip that you can download from the Course Resources) into Anypoint Studio and name it connect-various-systems.

 

Install the mock-servers dependency located in MUFundamentals4.x DIY Files zip into your local m2 repository to allow the project to run the mock FTP and mock Database servers that you will use for this project. For help, follow the step-by-step Install a Mule application as a dependency walkthrough in module 6.

Create a flow to read in gift cards 

Create a new Mule configuration file named process-visa-gift-cards. Create a flow named processVisaGiftCards to retrieve CSV files from a folder that Visa will use to transfer CSV files to this Mule application.

 

Use property placeholders to configure the file reader, so properties including the input and output folder can be changed when the Mule application is deployed to a different environment, such as to a production environment.

Note: Your operations team will set up a folder in a server where Visa can upload files. The server will host this Mule application in a customer-hosted Mule runtime. To learn how to deploy Mule applications to customer-hosted Mule runtimes, take the Anypoint Platform Operations: Customer-Hosted Runtimes course.

Answer the following questions

·       Instead of listening to new files being written to a server, a database table will constantly get updated with new gift cards. What operation and configuration would you need to replace the On New File operation with to ensure new gift card records are processed only once?

Create a flow to insert files into the Food-N-Savings database

The Food-N-Savings partner has set up a Postgres database to consume gift cards. The table is named Giftcards and has fields named number, sourceID, balance, and createdOn. The sourceID is the Mule Bank's ID, which is MULEBANK-0949. Access to the Postgres database has not yet been allowed by the Food-N-Savings security team. To help you focus on just developing the Mule application, the starter project embeds an in-memory Java Derby database that automatically launches when you run the project. This database uses the same schema as the Postgres database, so it can be used instead of starting up a local Postgres instance during development.

Note: The configuration to run this in-memory Java Derby database also shows you how to use Spring beans in a Mule 4 application to run Java code when a Mule application starts and stops.

Create a new Mule configuration file named foodnsavings.xml to send gift cards to the Food-N-Savings partner. A sample Visa CSV file for Food-N-Savings is included in the starter project's src/test/resources folder. The CSV file has the following format:

card_no,processor_id,amount_granted,partner

830804083,243796,70,Food and Savings

703530278,884400,150,Food and Savings

Add an environment variable to your Mule application and then add logic to the Food-N-Savings Mule configuration file's flow to use the Java Derby database if the environment is not set to prod. 

Note: Remember that each flow that sends files to a partner must ultimately be an individual Mule application that can be deployed in the same region as the partner's server.

Add logic to the processVisaGiftCards flow to test the CSV file's partner column. If the partner value is "Food N Savings", route the event to the processing flow in foodnsavings.xml using a Flow Reference component.

Note: In the next exercise, you will replace the Flow Reference with a JMS Publish operation to a JMS queue and add a corresponding JMS Listener to the foodnsavings.xml file's flow.

 

In the foodnsavings.xml flow, use the following information and insert each CSV file row into the Giftcards table in the database:

 

Visa CSV file	Food-N-Savings Giftcard database table	Values
card_no	number	The Visa gift card number
amount_granted	balance	The current Visa gift card balance
	sourceID	The Mule Bank ID "MULEBANK-0949"
	createdOn	The current datetime when this mapping occurs

 

Note: To perform the database operations safely (to avoid SQL injection), be sure to use parameterized queries to insert data into the table.

Create a flow to write files to the Meals-N-Go partner's FTP server

The Meals-N-Go partner has set up an FTP server to receive the gift cards. To help you focus on just developing the Mule application, the starter project embeds an in-memory FTP server that automatically launches when you run the project.

Note: The configuration to run this in-memory FTP server also shows you how to use Spring beans in a Mule 4 application to run Java code when a Mule application starts and stops.

Create a new Mule configuration file named mealsngo.xml. Create a flow to process the Visa gift card CSV file by transforming the CSV file to the Meals-N-Go CSV format and then writing the transformed file to a Meals-N-Go managed FTP server.

 

Add logic to the processVisaGiftCards flow to test the CSV file's partner column. If the partner value is "Meals n Go", route the event to the processing flow in mealsngo.xml using a Flow Reference component.

Note: In the next exercise, you will replace the Flow Reference with a JMS Publish operation to a JMS queue and add a corresponding JMS Listener to the mealsngo.xml file's flow.

 

In the mealsngo.xml flow, use the following information and insert each row of the CSV file into the FTP server:

 

Visa CSV file	Meals-N-Go CSV file	Values
card_no	gc_card_number	The Visa gift card number
amount_granted	gc_balance	The current Visa gift card balance
	origin	The Mule Bank ID "MULEBANK-0949"
	card_type	"VISA"
	expiration	3 months after the current datetime when this mapping occurs, in milliseconds since Unix Epoch

 

The uploaded files must follow the naming pattern: MULEBANK-gc-{datetime}.csv, where {datetime} is the current datetime when the file is received by the process-visa-giftcards Mule application and must be in milliseconds since 1970 January 1 (Unix Epoch).

Create a flow to send a payload to the Online Retail Plus partner

Create a new Mule configuration file named oretailplus.xml. Create a flow to process the Visa gift card CSV file by transforming payload into a CSV file.

 

Add logic to the processVisaGiftCards flow to test the CSV file's partner column. If the partner value is "Online Retail Plus", route the event to the processing flow in oretailplus.xml using a Flow Reference component.

Note: In the next exercise, you will replace the Flow Reference with a JMS Publish operation to a JMS queue and add a corresponding JMS Listener to the oretailplus.xml file's flow.

In the oretailplus.xml flow, use the following information and convert the entire Visa CSV file with the following mapping:

 

Visa CSV file	Online Retail Plus message	Values
card_no	cardNo	The Visa gift card number
amount_granted	amount	The current Visa gift card balance
	bankOriginationID	The Mule Bank ID "MULEBANK-0949"

 

Finally, write out a copy of the payload to a local CSV file. The CSV files should be named MULEBANK-{partner3.name}-{datetime}-{count}.csv, where {count} is the record count for the CSV file and {datetime} is the current date and time in milliseconds since Unix Epoch.

Handle invalid partners

Write out a report to a file if there is a partner that is not Food-N-Savings, Meals-N-Go, or Online Retail Plus.

Verify your solution

Import the solution /files/module12/connectors-mod12-various-systems-part1-solution.jar deployable archive file (in the MUFundamentals4.x DIY Files zip that you can download from the Course Resources) and compare your solution.